using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using Entities;
using IDAL;

namespace SQLDBDAL
{
    public class ReviewDAO : IReviewDAO
    {
        private const string SQL_SELECT_REVIEW = "SELECT * FROM Review";
        private const string SQL_SELECT_REVIEW_BY_PRODUCT_ID = " SELECT * FROM Review WHERE ProductID=@ProductID";
        private const string SQL_INSERT_REVIEW = "INSERT INTO Review(ProductID, Username, ReviewTitle, Contents, ReviewRating, ReviewDate) VALUES(@ProductID, @Username, @ReviewTitle, @Contents, @ReviewRating, GETDATE())";
        private const string SQL_DELETE_REVIEW = "DELETE FROM Review WHERE ReviewID=@ReviewID";


        // Parameters
        private const string PARM_REVIEW_REVIEW_ID = "@ReviewID";
        private const string PARM_REVIEW_PRODUCT_ID = "@ProductID";
        private const string PARM_REVIEW_USERNAME = "@Username";
        private const string PARM_REVIEW_TITLE = "@ReviewTitle";
        private const string PARM_REVIEW_CONTENT = "@Contents";
        private const string PARM_REVIEW_RATING = "@ReviewRating";
        
        #region IReviewDAO Members

        public bool Delete(ReviewInfo review)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] { new SqlParameter(PARM_REVIEW_REVIEW_ID, SqlDbType.Int) };
            parms[0].Value = review.ReviewID;

            try
            {
                int result = 0;
                result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING,
                    CommandType.Text, SQL_DELETE_REVIEW, parms);
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public IList<ReviewInfo> GetAll()
        {
            IList<ReviewInfo> revIn = new List<ReviewInfo>();
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_SELECT_REVIEW, null))
            {
                while (reader.Read())
                {
                    ReviewInfo review = new ReviewInfo();
                    review.ReviewID = reader.GetInt32(0);
                    review.ProductID = reader.GetInt32(1);
                    review.UserName = reader.GetString(2);
                    review.ReviewTitle = reader.GetString(3);
                    review.Contents = reader.GetString(4);
                    if (!reader.IsDBNull(5))
                        review.ReviewRating = reader.GetInt32(5);
                    else
                        review.ReviewRating = 0;
                    review.ReviewDate = reader.GetDateTime(6);
                    review.ReviewStar = SQLDBHelper.GetStar(review.ReviewRating);
                    revIn.Add(review);
                }
            }
            return revIn;
        }


        public IList<ReviewInfo> GetByProductID(string productID)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] { new SqlParameter(PARM_REVIEW_PRODUCT_ID, SqlDbType.Int) };

            if (string.IsNullOrEmpty(productID))
                parms[0].Value = 0;
            else
                parms[0].Value = int.Parse(productID);

            IList<ReviewInfo> revIn = new List<ReviewInfo>();
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_SELECT_REVIEW_BY_PRODUCT_ID, parms))
            {
                while (reader.Read())
                {
                    ReviewInfo rev = new ReviewInfo();
                    rev.ReviewID = reader.GetInt32(0);
                    rev.ProductID = reader.GetInt32(1);
                    rev.UserName = reader.GetString(2);
                    rev.ReviewTitle = reader.GetString(3);
                    rev.Contents = reader.GetString(4);
                    if (!reader.IsDBNull(5))
                        rev.ReviewRating = reader.GetInt32(5);
                    else
                        rev.ReviewRating = 0;
                    rev.ReviewDate = reader.GetDateTime(6);
                    rev.ReviewStar = SQLDBHelper.GetStar(rev.ReviewRating);

                    revIn.Add(rev);
                }
            }
            return revIn;
        }

        public bool Insert(ReviewInfo review)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] {
											 new SqlParameter(PARM_REVIEW_PRODUCT_ID, SqlDbType.Int),
											 new SqlParameter(PARM_REVIEW_USERNAME, SqlDbType.NVarChar),
                                             new SqlParameter(PARM_REVIEW_TITLE, SqlDbType.NVarChar),
                                             new SqlParameter(PARM_REVIEW_CONTENT, SqlDbType.NText),
                                             new SqlParameter(PARM_REVIEW_RATING, SqlDbType.Int) };

            parms[0].Value = review.ProductID;
            parms[1].Value = review.UserName;
            parms[2].Value = review.ReviewTitle;
            parms[3].Value = review.Contents;
            parms[4].Value = review.ReviewRating;

            try
            {
                int result = 0;
                SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
                result = SQLDBHelper.ExecuteNonQuery(conn, CommandType.Text, SQL_INSERT_REVIEW, parms);
                SQLDBHelper.SetNewIdentity(conn, review.ReviewID);
                return (result > 0 ? true : false);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion
    }
}
